﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebService.VO;
using System.Data;

namespace WebService.DAO
{
    public class ItemDAO
    {
        private dbConnection conn;
        /// <summary>
        /// khởi tạo đối tượng xử lý dl
        /// </summary>
        public ItemDAO()
        {
            conn = new dbConnection();
        }
        /// <summary>
        /// lấy danh sách các địa điểm
        /// </summary>
        /// <returns></returns>

        public DataTable getItemsList(string language)
        {
            if (language.Equals("vi"))
            {
                return conn.GetDataTable("Select [ItemID],[ItemName],[CategoryID],[Rate],[Address],[Phone],[Email],[Note],[X],[Y],[Image] From tbl_Items Where Checked ='true'");
            }
            return conn.GetDataTable("Select a.[ItemID],b.[ItemName] as ItemName,[CategoryID],[Rate],b.[Address] as Address,[Phone],[Email],b.[Note] as Note,[X],[Y],[Image] From tbl_Items a, tbl_ItemsTranslation b Where a.ItemID=b.ItemID And Checked = 'true' And Language='" + language + "'");
        }

        /// <summary>
        /// lấy danh sách các địa điểm theo loai
        /// </summary>
        /// <returns></returns>

        public DataTable getItemsList(int id, string language)
        {
            if (language.Equals("vi"))
            {
                return conn.GetDataTable("Select [ItemID],[ItemName],[CategoryID],[Rate],[Address],[Phone],[Email],[Note],[X],[Y],[Image] From tbl_Items Where Checked = 'true' And [CategoryID]=" + id);
            }
            return conn.GetDataTable("Select a.[ItemID],b.[ItemName] as ItemName,[CategoryID],[Rate],b.[Address] as Address,[Phone],[Email],b.[Note] as Note,[X],[Y],[Image] From tbl_Items a, tbl_ItemsTranslation b Where a.ItemID=b.ItemID And  Checked = 'true' And Language='" + language + "' And [CategoryID]=" + id);
        }

        /// <summary>
        /// Tim kiem
        /// </summary>
        /// <returns></returns>
        public DataTable search(string keyword, string language)
        {
            string temp = Utility.convertToUnSign3(keyword);
            if (language.Equals("vi"))
            {
                return conn.GetDataTable("Select [ItemID],[ItemName],[CategoryID],[Rate],[Address],[Phone],[Email],[Note],[X],[Y],[Image] From tbl_Items Where  Checked = 'true' And dbo.fChuyenCoDauThanhKhongDau(ItemName) like '%" + temp + "%'");
            }
            return conn.GetDataTable("Select a.[ItemID],b.[ItemName] as ItemName,[CategoryID],[Rate],b.[Address] as Address,[Phone],[Email],b.[Note] as Note,[X],[Y],[Image] From tbl_Items a, tbl_ItemsTranslation b Where  Checked = 'true' And a.ItemID=b.ItemID And Language='" + language + "' And dbo.fChuyenCoDauThanhKhongDau(b.ItemName) like '%" + temp + "%'");
        }

        /// <summary>
        /// kiem tra Item da ton tai hay chua
        /// </summary>
        /// <returns></returns>
        public ItemVO getItem(string id, string language)
        {
            ItemVO item=new ItemVO();
            DataTable dt;
            if (language.Equals("vi"))
            {
                dt = conn.GetDataTable("Select [ItemID],[ItemName],[CategoryID],[Rate],[Address],[Phone],[Email],[Note],[X],[Y],[Image], [CountRate] From tbl_Items Where ItemID='" + id + "'");
            }
            else
            {
                dt = conn.GetDataTable("Select a.[ItemID],b.[ItemName] as ItemName,[CategoryID],[Rate],b.[Address] as Address,[Phone],[Email],b.[Note] as Note,[X],[Y], [Image], [CountRate] From tbl_Items a, tbl_ItemsTranslation b Where a.ItemID=b.ItemID And Language='" + language + "' And ItemID='" + id + "'");
            }
            if (dt != null)
            {
                if (dt.Rows[0] != null)
                {
                    item.ItemId = Convert.ToInt16(dt.Rows[0]["ItemID"]);
                    if (dt.Rows[0]["ItemName"].ToString() != null)
                    {
                        item.ItemName = dt.Rows[0]["ItemName"].ToString();
                    }
                    else
                    {
                        item.ItemName = "NULL";
                    }
                    if (dt.Rows[0]["CategoryID"] != null)
                    {
                        CategoryDAO cateDAO = new CategoryDAO();
                        CategoryVO cate = new CategoryVO();
                        cate = cateDAO.getCategory(dt.Rows[0]["CategoryID"].ToString());
                        item.Category = cate;
                    }
                    if (dt.Rows[0]["Rate"] != null)
                    {
                        item.Rate = Convert.ToDouble(dt.Rows[0]["Rate"].ToString());
                    }
                    if (dt.Rows[0]["Address"] != null)
                    {
                        item.Address = dt.Rows[0]["Address"].ToString();
                    }
                    if (dt.Rows[0]["Phone"] != null)
                    {
                        item.Phone = dt.Rows[0]["Phone"].ToString();
                    }
                    if (dt.Rows[0]["Email"] != null)
                    {
                        item.Email = dt.Rows[0]["Email"].ToString();
                    }
                    if (dt.Rows[0]["Note"] != null)
                    {
                        item.Note = dt.Rows[0]["Note"].ToString();
                    }
                    if (dt.Rows[0]["X"] != null)
                    {
                        item.X = Convert.ToDouble(dt.Rows[0]["X"].ToString());
                    }
                    if (dt.Rows[0]["Y"] != null)
                    {
                        item.Y = Convert.ToDouble(dt.Rows[0]["Y"].ToString());
                    }
                    if (dt.Rows[0]["Image"] != null)
                    {
                        item.Image = dt.Rows[0]["Image"].ToString();
                    }
                    if (dt.Rows[0]["CountRate"] != null)
                    {
                        item.CountRate = Convert.ToInt32(dt.Rows[0]["CountRate"].ToString());
                    }
                }
            }
            return item;
        }
        /// <summary>
        /// Kiem tra dia diem da ton tai bang ten va vi tri cua dia diem do 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="lat"></param>
        /// <param name="lng"></param>
        /// <returns></returns>
        public ItemVO getItem(string name, double lat, double lng)
        {
            ItemVO item = new ItemVO();
            DataTable dt = conn.GetDataTable("Select * From tbl_Items WHERE ItemName='" + name + "' AND (X='" + lat + "' AND Y='" + lng + "')");
            if (dt != null)
            {
                if (dt.Rows[0] != null)
                {
                    item.ItemId = Convert.ToInt16(dt.Rows[0]["ItemID"]);
                    if (dt.Rows[0]["ItemName"].ToString() != null)
                    {
                        item.ItemName = dt.Rows[0]["ItemName"].ToString();
                    }
                    else
                    {
                        item.ItemName = "NULL";
                    }
                    if (dt.Rows[0]["CategoryID"] != null)
                    {
                        CategoryDAO cateDAO = new CategoryDAO();
                        CategoryVO cate = new CategoryVO();
                        cate = cateDAO.getCategory(dt.Rows[0]["CategoryID"].ToString());
                        item.Category = cate;
                    }
                    if (dt.Rows[0]["Rate"] != null)
                    {
                        item.Rate = Convert.ToDouble(dt.Rows[0]["Rate"].ToString());
                    }
                    if (dt.Rows[0]["Address"] != null)
                    {
                        item.Address = dt.Rows[0]["Address"].ToString();
                    }
                    if (dt.Rows[0]["Phone"] != null)
                    {
                        item.Phone = dt.Rows[0]["Phone"].ToString();
                    }
                    if (dt.Rows[0]["Email"] != null)
                    {
                        item.Email = dt.Rows[0]["Email"].ToString();
                    }
                    if (dt.Rows[0]["Note"] != null)
                    {
                        item.Note = dt.Rows[0]["Note"].ToString();
                    }
                    if (dt.Rows[0]["X"] != null)
                    {
                        item.X = Convert.ToDouble(dt.Rows[0]["X"].ToString());
                    }
                    if (dt.Rows[0]["Y"] != null)
                    {
                        item.Y = Convert.ToDouble(dt.Rows[0]["Y"].ToString());
                    }
                    if (dt.Rows[0]["Image"] != null)
                    {
                        item.Image = dt.Rows[0]["Image"].ToString();
                    }
                    if (dt.Rows[0]["CountRate"] != null)
                    {
                        item.CountRate = Convert.ToInt16(dt.Rows[0]["CountRate"].ToString());
                    }
                }
            }
            return item;
        }

        public bool check(string name, string lat, string lng, int categoryid)
        {
            string sqlSelect = "Select count(*) From tbl_Items Where ItemName=@ItemName AND (X=@Lat AND Y=@Lng) AND CategoryID=@CategoryID";
            IDictionary<string, string> d = new Dictionary<string, string>();

            d.Add("@ItemName", name);
            d.Add("@Lat", lat);
            d.Add("@Lng", lng);
            d.Add("@CategoryID", categoryid.ToString());

            int result = conn.ExecuteScalar(sqlSelect, d, false);
            if (result > 0)
                return true;
            return false;
        }


        public bool checklang(string itemid, string lang)
        {
            string sqlSelect = "Select count(*) From tbl_ItemsTranslation Where Language=@Lang AND ItemID=@ItemID";
            IDictionary<string, string> d = new Dictionary<string, string>();

            d.Add("@Lang", lang);
            d.Add("@ItemID", itemid);

            int result = conn.ExecuteScalar(sqlSelect, d, false);
            if (result > 0)
                return true;
            return false;
        }
        

        /// <summary>
        /// Thêm mới 1 địa điểm
        /// </summary>
        public void insert(ItemVO item)
        {
            string sqlInsert = "INSERT INTO tbl_Items VALUES(@ItemName, @CategoryID, @Rate," 
            +"@Address, @Phone, @Email, @Note, @X, @Y, @Image, @CountRate, @Checked)";

            IDictionary<string, string> d = new Dictionary<string, string>();

            d.Add("@ItemName", item.ItemName);
            d.Add("@CategoryID", item.Category.CategoryId.ToString());
            d.Add("@Rate", item.Rate.ToString());
            d.Add("@Address", item.Address);
            d.Add("@Phone", item.Phone);
            d.Add("@Email", item.Email);
            d.Add("@Note", item.Note);
            d.Add("@X", item.X.ToString());
            d.Add("@Y", item.Y.ToString());
            d.Add("@Image", item.Image);
            d.Add("@CountRate", item.CountRate.ToString());
            d.Add("@Checked", item.Check.ToString());

            conn.ExecuteQuery(sqlInsert, d, false);

        }

        /// <summary>
        /// Cập nhật 1 địa điểm
        /// </summary>
        public void update(ItemVO item)
        {
            string sqlInsert = "UPDATE tbl_Items"
               +"SET [ItemName] = @ItemName"
                 +" ,[CategoryID] = @CategoryID"
                  +",[Rate] = @Rate"
                  +",[Address] = @Address>"
                  +",[Phone] = @Phone"
                  +",[Email] = @Email"
                  +",[Note] = @Note"
                  +",[X] = @X"
                  +",[Y] = @Y"
                  +",[Image] = @Image>"
                  +",[CountRate] = @CountRate"
             + "WHERE [ItemName] = @ItemId";

            IDictionary<string, string> d = new Dictionary<string, string>();
            d.Add("@ItemId", item.ItemId.ToString());
            d.Add("@ItemName", item.ItemName);
            d.Add("@CategoryID", item.Category.CategoryId.ToString());
            d.Add("@Rate", item.Rate.ToString());
            d.Add("@Address", item.Address);
            d.Add("@Phone", item.Phone);
            d.Add("@Email", item.Email);
            d.Add("@Note", item.Note);
            d.Add("@X", item.X.ToString());
            d.Add("@Y", item.Y.ToString());
            d.Add("@Image", item.Image);
            d.Add("@CountRate", item.CountRate.ToString());

            conn.ExecuteQuery(sqlInsert, d, false);

        }

        public void updateRating(double newRate, int CountRate, string ItemID)
        {
            string strUpdateRate = "Update tbl_Items set Rate=@newrate, CountRate=@countrate where ItemID=@itemid";
            IDictionary<string, string> d = new Dictionary<string, string>();
            d.Add("@itemid", ItemID.ToString());
            d.Add("@newrate", newRate.ToString());
            d.Add("@countrate", CountRate.ToString());
            conn.ExecuteQuery(strUpdateRate, d, false);
        }


        /// <summary>
        /// Xóa thông tin một địa điểm
        /// </summary>
        public void delete(string id)
        {
            string sql = "DELETE FROM tbl_Items WHERE itemid = @id";

            IDictionary<string, string> d = new Dictionary<string, string>();

            d.Add("@id", id);
            conn.ExecuteQuery(sql, d, false);
        }

    }
}